/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package unc.pds.auth;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import javax.ejb.CreateException;
import javax.ejb.EJBException;
import javax.ejb.EntityBean;
import javax.ejb.EntityContext;
import javax.ejb.FinderException;
import javax.ejb.SessionBean;
import javax.ejb.SessionContext;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import unc.pds.data.Consts;
import unc.pds.data.KeyGen;
import unc.pds.data.RootDataBeanRemote;
import unc.pds.data.RootDataBeanRemoteHome;
import unc.pds.exc.NotEnoughDataException;
import unc.pds.exc.PDSSecurityException;
import unc.pds.exc.UserAlreadyExistException;
import unc.pds.model.Model;
import unc.pds.model.ModelFactory;
import unc.pds.model.RoleRemote;
import unc.pds.model.RoleRemoteHome;
import unc.pds.model.UserRemote;
import unc.pds.model.UserRemoteHome;
import unc.pds.util.Arch;
import unc.pds.util.ArchImpl;

/**
 *
 * @author AIN
 */
public class SecurityB implements EntityBean, IPDSSecurity {

    private EntityContext context;
    //private SessionContext context;
    private Long sessionKey;
    private UserRemote curUser = null;
    private Hashtable permissionCache;
    // <editor-fold defaultstate="collapsed" desc="EJB infrastructure methods. Click the + sign on the left to edit the code.">;
    // TODO Add code to acquire and use other enterprise resources (DataSource, JMS, enterprise bean, Web services)
    // TODO Add business methods or web service operations

    /**
     * @see javax.ejb.SessionBean#setSessionContext(javax.ejb.SessionContext)
     */
    /*public void setSessionContext(SessionContext aContext) {
    context = aContext;
    }*/
    /**
     * @see javax.ejb.SessionBean#ejbActivate()
     */
    public void ejbActivate() {
    }

    /**
     * @see javax.ejb.SessionBean#ejbPassivate()
     */
    public void ejbPassivate() {
    }

    /**
     * @see javax.ejb.SessionBean#ejbRemove()
     */
    public void ejbRemove() {
    }

    // </editor-fold>;
    /**
     * See section 7.10.3 of the EJB 2.0 specification
     * See section 7.11.3 of the EJB 2.1 specification
     */
    /*public void ejbCreate() {
    // TODO implement ejbCreate if necessary, acquire resources
    // This method has access to the JNDI context so resource aquisition
    // spanning all methods can be performed here such as home interfaces
    // and data sources.
    }*/

    /*public void ejbCreate(Long nsessionKey) {
    sessionKey = nsessionKey;
    permissionCache = new Hashtable();
    }*/
    // Add business logic below. (Right-click in editor and choose
    // "Insert Code > Add Business Method" or "Web Service > Add Operation")
    private Connection getConnection() throws NamingException, SQLException {
        DataSource ds = (DataSource) (new InitialContext()).lookup("oracle/SOS");
        return ds.getConnection();
    }

    public boolean setPermission(Long objkey, String entity, String act, String level) throws RemoteException {
        if (!getPermission(objkey, "write")) {
            return false;
        }
        try {
            String exlevel = getPermLivel(objkey, entity, act);
            //проверяем параметр на наличие
            if (exlevel != null) {
                if (exlevel.compareTo(level) == 0) {
                    return true;
                }
                //параметр есть и отличен, значит обновить параметр
                Connection conn = getConnection();
                ResultSet ans = null;
                Statement query = null;
                try {
                    query = conn.createStatement();
                    return (query.executeUpdate("UPDATE UPERMISSION SET "
                            + "REQ_LEVEL='" + level + "' "
                            + "WHERE "
                            + "OBJECT_ID=" + objkey + " AND "
                            + "ENTITY='" + entity + "' AND"
                            + "UACTION='" + act + "'") > 0);

                } finally {
                    ans.close();
                    query.close();
                    conn.close();
                }
            } else {
                Connection conn = getConnection();
                ResultSet ans = null;
                Statement query = null;
                try {
                    query = conn.createStatement();
                    ans = query.executeQuery("INSERT INTO UPERMISSION ("
                            + "OBJECT_ID,ENTITY,UACTION,REQ_LEVEL) VALUES ("
                            + objkey + ",'"
                            + entity + "','"
                            + act + "','"
                            + level + "')");
                    return ans.next();
                } finally {
                    ans.close();
                    query.close();
                    conn.close();
                }
            }
        } catch (Exception e) {
            throw new RemoteException(e.getLocalizedMessage());
        }
    }

    public boolean setPermission(Long objkey, String entity, String field, String act, String level) throws RemoteException {
        if (!getPermission(objkey, "write")) {
            return false;
        }
        try {
            String exlevel = getPermLivel(objkey, entity, act);
            //проверяем параметр на наличие
            if (exlevel != null) {
                if (exlevel.compareTo(level) == 0) {
                    return true;
                }
                //параметр есть и отличен, значит обновить параметр
                Connection conn = getConnection();
                ResultSet ans = null;
                Statement query = null;
                try {
                    query = conn.createStatement();
                    return (query.executeUpdate("UPDATE UPERMISSION SET "
                            + "REQ_LEVEL='" + level + "' "
                            + "WHERE "
                            + "OBJECT_ID=" + objkey + " AND "
                            + "ENTITY='" + entity + "' AND"
                            + "UACTION='" + act + "' AND "
                            + "EN_FIELD='" + field + "'") > 0);

                } finally {
                    ans.close();
                    query.close();
                    conn.close();
                }
            } else {
                Connection conn = getConnection();
                ResultSet ans = null;
                Statement query = null;
                try {
                    query = conn.createStatement();
                    ans = query.executeQuery("INSERT INTO UPERMISSION ("
                            + "OBJECT_ID,ENTITY,UACTION,REQ_LEVEL,EN_FIELD) VALUES ("
                            + objkey + ",'"
                            + entity + "','"
                            + act + "','"
                            + level + "','"
                            + field + "')");
                    return ans.next();
                } finally {
                    ans.close();
                    query.close();
                    conn.close();
                }
            }
        } catch (Exception e) {
            throw new RemoteException(e.getLocalizedMessage());
        }
    }

    public boolean getCrPermission(Long objkey, String entity) throws RemoteException {
        //проверяем системные роли
        int sysperm = getSysPermission(entity, "create");
        if (sysperm == SecurityST.SECURITY_ACCESS) {
            return true;
        }
        if (sysperm == SecurityST.SECURITY_DENIED) {
            return false;
        }
        //родителей нет, системных разрешений нет, идем в топку
        if (objkey == null) {
            return false;
        }
        //проверяем owner роли
        try {
            RootDataBeanRemoteHome rdbrh = (RootDataBeanRemoteHome) (new InitialContext()).lookup("ejb/RootDataBean");
            RootDataBeanRemote rdbr = rdbrh.findByPrimaryKey(objkey);
            if (rdbr.isOwnerSomehow(getCurrentUser().getKey())) {
                return true;
            }
        } catch (Exception ex) {
            throw new RemoteException("SB.getPermission " + ex.getLocalizedMessage());
        }
        //проверяем логические связи
        Model m1 = ModelFactory.getByKey(objkey, sessionKey);
        if (teaCanDoThat(objkey, getPermLivel(objkey, entity, "create"))) {
            return true;
        }
        while (m1.getParent() != null) {
            m1 = m1.getParent();
            if (teaCanDoThat(objkey, getPermLivel(m1.getKey(), entity, "create"))) {
                return true;
            }
        }

        UserRemote user = null;
        try {
            user = ((UserRemoteHome) (new InitialContext()).lookup("ejb/User")).create(getCurrentSysRole(), sessionKey);
        } catch (Exception ex) {
            System.err.println("WARNING: no user for " + getCurrentSysRole() + ". error: " + ex.getLocalizedMessage());
        }
        if (user != null) {
            if (teaCanDoThat(objkey, getPermLivel(user.getKey(), entity, "create"))) {
                return true;
            }
        }
        return SecurityST.SECURITY_DEFAULT_RES;
    }

    public boolean getPermission(Long objkey, String field, String act) throws RemoteException {

        //проверяем системные роли
        if (Consts.DEBUG_MODE) System.out.println("SecurityB.getPermossion for " + ModelFactory.getByKey(objkey, sessionKey).getType() + " for " + act);
        int sysperm = getSysPermission(ModelFactory.getByKey(objkey, sessionKey).getType(), act);
        if (Consts.DEBUG_MODE) System.out.println("        system perm is " + sysperm);
        if (sysperm == SecurityST.SECURITY_ACCESS) {
            return true;
        }
        if (sysperm == SecurityST.SECURITY_DENIED) {
            return false;
        }


        //проверяем owner роли
        if (checkOwnerPermission(objkey)) {
            return true;
        }

        //проверяем логические связи
        if (checkLogicPermission(objkey, field, act)) {
            return true;
        }
        //ок, не вышло проверить связи по полям, попробуем по сущности
        if (checkLogicPermission(objkey, act)) {
            return true;
        }

        //последний рубеж. проверка подобных свойств у дефолтовой роли
        if (checkDefaultLogicPermission(objkey, field, act)) {
            return true;
        }
        if (checkDefaultLogicPermission(objkey, act)) {
            return true;
        }

        if (Consts.DEBUG_MODE) System.out.println("SecB.getPermission.return default " + SecurityST.SECURITY_DEFAULT_RES);
        return SecurityST.SECURITY_DEFAULT_RES;
    }

    public boolean getPermission(Long objkey, String act) throws RemoteException {

        //проверяем системные роли
        if (Consts.DEBUG_MODE) System.out.println("SecurityB.getPermossion for " + ModelFactory.getByKey(objkey, sessionKey).getType() + " for " + act);
        int sysperm = getSysPermission(ModelFactory.getByKey(objkey, sessionKey).getType(), act);
        if (Consts.DEBUG_MODE) System.out.println("        system perm is " + sysperm);
        if (sysperm == SecurityST.SECURITY_ACCESS) {
            return true;
        }
        if (sysperm == SecurityST.SECURITY_DENIED) {
            return false;
        }


        //проверяем owner роли
        if (checkOwnerPermission(objkey)) {
            return true;
        }

        //проверяем логические связи
        if (checkLogicPermission(objkey, act)) {
            return true;
        }

        //последний рубеж. проверка подобных свойств у дефолтовой роли
        if (checkDefaultLogicPermission(objkey, act)) {
            return true;
        }


        if (Consts.DEBUG_MODE) System.out.println("SecB.getPermission.return default " + SecurityST.SECURITY_DEFAULT_RES);
        return SecurityST.SECURITY_DEFAULT_RES;
    }

    private boolean checkOwnerPermission(Long objkey) throws RemoteException {
        if (Consts.DEBUG_MODE) System.out.println("SecB.getPermission.OwnerCheck for " + objkey);
        try {
            RootDataBeanRemoteHome rdbrh = (RootDataBeanRemoteHome) (new InitialContext()).lookup("ejb/RootDataBean");
            //if (Consts.DEBUG_MODE) System.out.println("        try found");
            RootDataBeanRemote rdbr = rdbrh.findByPrimaryKey(objkey);
            //if (Consts.DEBUG_MODE) System.out.println("        load bean");
            if (rdbr.isOwnerSomehow(getCurrentUser().getKey())) {
                if (Consts.DEBUG_MODE) System.out.println("SecB.isOwner: current user is owner for " + objkey);
                return true;
            } else {
                return false;
            }
        } catch (Exception ex) {
            if (Consts.DEBUG_MODE) System.out.println("ERROR: SB.getPermission.isOwner: " + ex.getLocalizedMessage());
            throw new RemoteException("ERROR: SB.getPermission.isOwner: " + ex.getLocalizedMessage());
        }
    }

    private boolean checkLogicPermission(Long objkey, String act) throws RemoteException {
        if (Consts.DEBUG_MODE) System.out.println("SecB.getPermission.logicCheck for " + objkey);
        //проверяем логические связи
        Model m1 = ModelFactory.getByKey(objkey, sessionKey);
        String entity = m1.getType();
        if (Consts.DEBUG_MODE) System.out.print("      type is " + entity);
        if (teaCanDoThat(objkey, getPermLivel(objkey, entity, act))) {
            return true;
        }
        while (m1.getParent() != null) {
            m1 = m1.getParent();
            if (teaCanDoThat(objkey, getPermLivel(m1.getKey(), entity, act))) {
                return true;
            }
        }
        return false;
    }

    private boolean checkLogicPermission(Long objkey, String field, String act) throws RemoteException {
        if (Consts.DEBUG_MODE) System.out.println("SecB.getPermission.logicCheck for " + objkey);
        //проверяем логические связи
        Model m1 = ModelFactory.getByKey(objkey, sessionKey);
        String entity = m1.getType();
        if (Consts.DEBUG_MODE) System.out.print("      type is " + entity);
        if (teaCanDoThat(objkey, getPermLivel(objkey, entity, field, act))) {
            return true;
        }
        while (m1.getParent() != null) {
            m1 = m1.getParent();
            if (teaCanDoThat(objkey, getPermLivel(m1.getKey(), entity, field, act))) {
                return true;
            }
        }
        return false;
    }

    private boolean checkDefaultLogicPermission(Long objkey, String act) throws RemoteException {
        if (Consts.DEBUG_MODE) System.out.println("SecB.getPermission.defaultCheck for " + objkey);
        UserRemote user = null;
        Model m1 = ModelFactory.getByKey(objkey, sessionKey);
        String entity = m1.getType();
        try {
            user = ((UserRemoteHome) (new InitialContext()).lookup("ejb/User")).create(getCurrentSysRole(), sessionKey);
        } catch (Exception ex) {
            System.err.println("WARNING: no user for " + getCurrentSysRole() + ". error: " + ex.getLocalizedMessage());
        }
        if (user != null) {
            if (teaCanDoThat(objkey, getPermLivel(user.getKey(), entity, act))) {
                return true;
            }
        }
        return false;
    }

    private boolean checkDefaultLogicPermission(Long objkey, String field, String act) throws RemoteException {
        if (Consts.DEBUG_MODE) System.out.println("SecB.getPermission.defaultCheck for " + objkey);
        UserRemote user = null;
        Model m1 = ModelFactory.getByKey(objkey, sessionKey);
        String entity = m1.getType();
        try {
            user = ((UserRemoteHome) (new InitialContext()).lookup("ejb/User")).create(getCurrentSysRole(), sessionKey);
        } catch (Exception ex) {
            System.err.println("WARNING: no user for " + getCurrentSysRole() + ". error: " + ex.getLocalizedMessage());
        }
        if (user != null) {
            if (teaCanDoThat(objkey, getPermLivel(user.getKey(), entity, field, act))) {
                return true;
            }
        }
        return false;
    }

    //проверяет может ли сущность дать юзеру достатоное право 
    private boolean teaCanDoThat(Long objkey, String req_role) throws RemoteException {
        if (Consts.DEBUG_MODE) System.out.println("SecB.teaCanDoThat: req_role " + req_role);
        if (req_role == null) {
            return false;
        }
        try {
            List roles = ModelFactory.getByKey(objkey, sessionKey).getRolesForUser(getCurrentUser().getKey());
            if ((roles != null)&&(!roles.isEmpty())) {
                if (Consts.DEBUG_MODE) System.out.println("        get role list for current user " + roles.size());
            } else {
                Arch doc = new ArchImpl("root", null);
                doc.addChild(new ArchImpl(Consts.ATTR__ROLE_NAME, Consts.ROLE_ANY));
                RootDataBeanRemoteHome rdbrh = (RootDataBeanRemoteHome) (new InitialContext()).lookup("ejb/RootDataBean");
                RootDataBeanRemote rr = rdbrh.findByFieldSet(doc, Consts.OBJECT_TYPE__ROLE);
                rr.setSessionKey(sessionKey);
                RoleRemoteHome rrh = (RoleRemoteHome) (new InitialContext()).lookup("ejb/Role");
                RoleRemote temprole = rrh.create(rr, sessionKey);
                roles = new ArrayList();
                roles.add(temprole);
                if (Consts.DEBUG_MODE) System.out.println("        get role list for current user. Create default role : any ");
            }

            Arch doc = new ArchImpl("root", null);
            doc.addChild(new ArchImpl(Consts.ATTR__ROLE_NAME, req_role));
            RootDataBeanRemoteHome rdbrh = (RootDataBeanRemoteHome) (new InitialContext()).lookup("ejb/RootDataBean");
            RootDataBeanRemote rr = rdbrh.findByFieldSet(doc, Consts.OBJECT_TYPE__ROLE);
            rr.setSessionKey(sessionKey);
            RoleRemoteHome rrh = (RoleRemoteHome) (new InitialContext()).lookup("ejb/Role");
            RoleRemote temprole = rrh.create(rr, sessionKey);
            if (Consts.DEBUG_MODE) System.out.println("        cast req_role");

            for (int i = 0; i < roles.size(); i++) {
                if (((RoleRemote) roles.toArray()[i]).isSuprOrEqual(temprole)) {
                    return true;
                }
            }
            return false;
        } catch (Exception ex) {
            throw new RemoteException("teaCanDoThat: " + ex.getLocalizedMessage());
        }
    }

    //уникальные пермишны на каждый день
    private String getPermLivel(Long objkey, String entity, String act) throws RemoteException {
        if (Consts.DEBUG_MODE) System.out.println("SecB.getPermLevel");
        String cKey = objkey + entity + act;
        if (permissionCache.containsKey(cKey)) {
            return (String) permissionCache.get(cKey);
        }
        try {
            if (entity == null) {
                entity = ModelFactory.getByKey(objkey, sessionKey).getType();
            }
            //if (Consts.DEBUG_MODE) System.out.println("SecB.getPermLevel get conn");
            Connection conn = getConnection();
            Statement query = null;
            ResultSet ans = null;
            try {
                query = conn.createStatement();
                String querry = "SELECT * FROM UPERMISSION WHERE "
                        + "OBJECT_ID=" + objkey + " AND "
                        + "ENTITY='" + entity + "' AND "
                        + "UACTION='" + act + "' AND "
                        + "EN_FIELD IS NULL";
                if (Consts.DEBUG_MODE) System.out.println(querry);
                //if (Consts.DEBUG_MODE) System.out.println("SecB.getPermLevel try execute querry");
                ans = query.executeQuery(querry);
                //if (Consts.DEBUG_MODE) System.out.println("SecB.getPermLevel success");
                if (ans.next()) {
                    if (Consts.DEBUG_MODE) System.out.print("      is " + ans.getString("REQ_LEVEL"));
                    permissionCache.put(cKey, ans.getString("REQ_LEVEL"));
                    return ans.getString("REQ_LEVEL");
                } else {
                    if (Consts.DEBUG_MODE) System.out.print("      is null");
                    return null;
                }
            } finally {
                ans.close();
                query.close();
                conn.close();
            }
        } catch (Exception ex) {
            throw new RemoteException("SB.getPermission  " + ex.getLocalizedMessage());
        }
    }

    private String getPermLivel(Long objkey, String entity, String field, String act) throws RemoteException {
        if (Consts.DEBUG_MODE) System.out.println("SecB.getPermLevel");
        String cKey = objkey + entity + field + act;
        if (permissionCache.containsKey(cKey)) {
            return (String) permissionCache.get(cKey);
        }
        try {
            if (entity == null) {
                entity = ModelFactory.getByKey(objkey, sessionKey).getType();
            }
            //if (Consts.DEBUG_MODE) System.out.println("SecB.getPermLevel get conn");
            Connection conn = getConnection();
            Statement query = null;
            ResultSet ans = null;
            try {
                query = conn.createStatement();
                String querry = "SELECT * FROM UPERMISSION WHERE "
                        + "OBJECT_ID=" + objkey + " AND "
                        + "ENTITY='" + entity + "' AND "
                        + "UACTION='" + act + "' AND "
                        + "EN_FIELD='" + field + "'";
                if (Consts.DEBUG_MODE) System.out.println(querry);
                //if (Consts.DEBUG_MODE) System.out.println("SecB.getPermLevel try execute querry");
                ans = query.executeQuery(querry);
                //if (Consts.DEBUG_MODE) System.out.println("SecB.getPermLevel success");
                if (ans.next()) {
                    if (Consts.DEBUG_MODE) System.out.print("      is " + ans.getString("REQ_LEVEL"));
                    permissionCache.put(cKey, ans.getString("REQ_LEVEL"));
                    return ans.getString("REQ_LEVEL");
                } else {
                    if (Consts.DEBUG_MODE) System.out.print("      is null");
                    return null;
                }
            } finally {
                ans.close();
                query.close();
                conn.close();
            }
        } catch (Exception ex) {
            throw new RemoteException("SB.getPermission  " + ex.getLocalizedMessage());
        }
    }

    //системные пермишны, основанные на уровнях доступа в системе
    private int getSysPermission(String event, String act) throws RemoteException {
        return getSysPermissionFor(getCurrentSysRole(), event, act);
    }

    private int getSysPermissionFor(String role, String event, String act) throws RemoteException {
        if (Consts.DEBUG_MODE) System.out.println("SecurityB.getSysPermFor role is: " + role + " req act: " + act + " for event: " + event);
        try {
            Connection conn = getConnection();
            Statement query = null;
            ResultSet ans = null;
            try {
                query = conn.createStatement();
                String querry = "SELECT * FROM SYSPERM WHERE "
                        + "ROLE='" + role + "' AND "
                        + "OBJTYPE='" + event + "' AND "
                        + "UACTION='" + act + "'";
                ans = query.executeQuery(querry);
                if (ans.next()) {
                    String UACCESS = ans.getString("UACCESS");
                    if (UACCESS.compareTo(SecurityST.SECURITY_ACCESS_ST) == 0) {
                        if (Consts.DEBUG_MODE) System.out.println("        result is: " + SecurityST.SECURITY_ACCESS);
                        return SecurityST.SECURITY_ACCESS;
                    }
                    if (UACCESS.compareTo(SecurityST.SECURITY_DENIED_ST) == 0) {
                        if (Consts.DEBUG_MODE) System.out.println("        result is: " + SecurityST.SECURITY_DENIED);
                        return SecurityST.SECURITY_DENIED;
                    }
                } else {
                    if (Consts.DEBUG_MODE) System.out.println("        result is: " + SecurityST.SECURITY_DEFAULT);
                    return SecurityST.SECURITY_DEFAULT;
                }
                if (Consts.DEBUG_MODE) System.out.println("        result is: " + SecurityST.SECURITY_DEFAULT);
                return SecurityST.SECURITY_DEFAULT;
            } finally {
                ans.close();
                query.close();
                conn.close();
            }
        } catch (Exception ex) {
            throw new RemoteException("SB.getPermission  " + ex.getLocalizedMessage());
        }
    }

    private String getCurrentSysRole() throws RemoteException {
        if (Consts.DEBUG_MODE) System.out.println("SecB.getCurSysRole start " + sessionKey);
        if (isAnonimous()) {
            if (Consts.DEBUG_MODE) System.out.println("        result is: " + SecurityST.SECURITY_ANONIMOUS_ROLE);
            return SecurityST.SECURITY_ANONIMOUS_ROLE;
        }
        if (getCurrentUser() != null) {
            try {
                String role = "";
                Connection conn = getConnection();
                Statement query = null;
                ResultSet ans = null;
                try {
                    query = conn.createStatement();
                    String querry = "SELECT * FROM PRIVILEGED WHERE USERID=" + getCurrentUser().getKey();
                    ans = query.executeQuery(querry);
                    if (ans.next()) {
                        role = ans.getString("PRLEVEL");
                    } else {
                        role = SecurityST.SECURITY_DEFAULT_ROLE;
                    }
                    if (Consts.DEBUG_MODE) System.out.println("        result is: " + role);
                    return role;
                } finally {
                    ans.close();
                    query.close();
                    conn.close();
                }
            } catch (Exception ex) {
                throw new RemoteException("SB.getPermission  " + ex.getLocalizedMessage());
            }
        }
        if (Consts.DEBUG_MODE) System.out.println("        result is: " + SecurityST.SECURITY_ANONIMOUS_ROLE);
        return SecurityST.SECURITY_ANONIMOUS_ROLE;
    }

    public UserRemote getCurrentUser() throws RemoteException {
        if (sessionKey.compareTo(SecurityST.LoginSessionKey) == 0) {
            //логин насяника
            curUser = null;
        } else {
            if (curUser == null) {
                curUser = (UserRemote) ModelFactory.getByKey(sessionKey, sessionKey);
            }
        }
        return curUser;
    }

    public boolean isAnonimous() {
        if (sessionKey == null) {
            return true;
        } else {
            return false;
        }
    }

    public Long login(String nick, String password) throws RemoteException {
        try {
            Long object_type_id = getObjectTypeID(Consts.OBJECT_TYPE__USER);
            Long pass_attr_id = getAttributeID(Consts.ATTR__USER_PASSWORD, object_type_id);
            Long nick_attr_id = getAttributeID(Consts.ATTR__USER_NICKNAME, object_type_id);
            Connection conn = getConnection();
            ResultSet ans = null;
            Statement query = null;
            try {
                query = conn.createStatement();
                String sqlquerry = "SELECT * FROM PARAMS WHERE "
                        + Consts.PARAMS__ATTRIBUTE_ID + "=" + pass_attr_id + " AND "
                        + Consts.PARAMS__VALUE + "='" + password + "' AND "
                        + Consts.PARAMS__OBJECT_ID + " IN ("
                        + "SELECT " + Consts.PARAMS__OBJECT_ID + " FROM PARAMS WHERE "
                        + Consts.PARAMS__ATTRIBUTE_ID + "=" + nick_attr_id + " AND "
                        + Consts.PARAMS__VALUE + "='" + nick + "')";
                ans = query.executeQuery(sqlquerry);
                if (ans.next()) {
                    sessionKey = new Long(ans.getLong(Consts.PARAMS__OBJECT_ID));
                    return sessionKey;
                } else {
                    return null;
                }
            } finally {
                ans.close();
                query.close();
                conn.close();
            }
        } catch (Exception e) {
            throw new RemoteException(e.toString());
        }
    }

    public Long register(Arch doc) throws RemoteException, PDSSecurityException {
        if ((doc.getChild(Consts.ATTR__USER_NICKNAME) == null) || (doc.getChild(Consts.ATTR__USER_PASSWORD) == null)) {
            throw new NotEnoughDataException();
        }
        String nickname = doc.getChild(Consts.ATTR__USER_NICKNAME).getValue();
        if (userExist(nickname)) {
            throw new UserAlreadyExistException();

        }
        String type = Consts.OBJECT_TYPE__USER;
        Long object_type_id = getObjectTypeID(Consts.OBJECT_TYPE__USER);
        Long object_id = KeyGen.getInstance().getKey();
        //добавляем запись юзера в таблицу объектов
        try {
            Connection conn = getConnection();
            Statement query = null;
            try {
                String names = Consts.OBJECTS__OBJECT_ID + ","
                        + Consts.OBJECTS__OBJECT_TYPE_ID + ","
                        + Consts.OBJECTS__NAME;
                String values = ") VALUES ("
                        + object_id + "," + object_type_id + ",'" + nickname + "'";
                query = conn.createStatement();
                String endquerry = "INSERT INTO OBJECTS ("
                        + names
                        + values + ")";
                if (Consts.DEBUG_MODE) System.out.println(endquerry);
                if (query.executeUpdate(endquerry) == 0) {
                    throw new RemoteException("cannot create user: insert return 0");

                }
            } finally {
                query.close();
                conn.close();
            }
        } catch (Exception ex) {
            if (Consts.DEBUG_MODE) System.out.println("SecB.register: " + ex.getLocalizedMessage());
            throw new RemoteException("SecB.register: " + ex.getLocalizedMessage());
        }
        //заполняем поля в PARAMS
        try {
            for (int i = 0; i < doc.getChilds().length; i++) {
                Arch child = doc.getChilds()[i];
                if ((child.getName().compareTo(Consts.ARCH_F_NAME) != 0)
                        && (child.getName().compareTo(Consts.ARCH_F_PARENT) != 0)
                        && (child.getName().compareTo(Consts.ARCH_F_TYPE) != 0)) {
                    //addParameter(child.getName(), child.getValue());
                    try {
                        Long attr_id = getAttributeID(child.getName(), object_type_id);
                        Connection conn = getConnection();
                        ResultSet ans= null;
                        Statement query = null;
                        try {
                            query = conn.createStatement();
                            ans = query.executeQuery("INSERT INTO PARAMS ("
                                    + Consts.PARAMS__OBJECT_ID + ","
                                    + Consts.PARAMS__ATTRIBUTE_ID + ","
                                    + Consts.PARAMS__VALUE + ") VALUES ("
                                    + object_id + ","
                                    + attr_id + ",'"
                                    + child.getValue() + "')");
                        } finally {
                            ans.close();
                            query.close();
                            conn.close();
                        }
                    } catch (Exception ex) {
                        if (Consts.DEBUG_MODE) System.out.println("SecB.addUserParameter: " + ex.getLocalizedMessage());
                        throw new Exception("SecB.addUserParameter: " + ex.getLocalizedMessage());
                    }
                }
            }
        } catch (Exception ex) {
            if (Consts.DEBUG_MODE) System.out.println("SecB.register: " + ex.getLocalizedMessage());
            throw new RemoteException("SecB.register: " + ex.getLocalizedMessage());
        }
        sessionKey = object_id;
        return sessionKey;
    }

    private boolean userExist(String nickname) throws RemoteException {
        try {
            Long object_type_id = getObjectTypeID(Consts.OBJECT_TYPE__USER);
            Long nick_attr_id = getAttributeID(Consts.ATTR__USER_NICKNAME, object_type_id);
            Connection conn = getConnection();
            ResultSet ans = null;
            Statement query = null;
            try {
                query = conn.createStatement();
                String sqlquerry = "SELECT * FROM PARAMS WHERE "
                        + Consts.PARAMS__ATTRIBUTE_ID + "=" + nick_attr_id + " AND "
                        + Consts.PARAMS__VALUE + "='" + nickname + "'";
                ans = query.executeQuery(sqlquerry);
                if (ans.next()) {
                    return true;
                } else {
                    return false;
                }
            } finally {
                ans.close();
                query.close();
                conn.close();
            }
        } catch (Exception e) {
            throw new RemoteException(e.toString());
        }
    }

    private Long getObjectTypeID(String name) throws RemoteException {
        {
            try {
                Connection conn = getConnection();
                ResultSet ans = null;
                Statement query = null;
                try {
                    query = conn.createStatement();
                    ans = query.executeQuery("SELECT * FROM OBJECT_TYPES "
                            + "WHERE NAME='" + name + "'");
                    if (ans.next()) {
                        return new Long(ans.getLong(Consts.OBJECT_TYPES__ID));
                    } else {
                        throw new RemoteException("SecB.getObjTypeID: not such object_type " + name);
                    }
                } finally {
                    ans.close();
                    query.close();
                    conn.close();
                }
            } catch (Exception e) {
                throw new RemoteException("SecB.getObjTypeID: " + e.getLocalizedMessage());
            }
        }
    }

    private Long getAttributeID(String name, Long object_type_id) throws FinderException, RemoteException {

        try {
            Connection conn = getConnection();
            ResultSet ans = null;
            Statement query  =null;
            try {
                query = conn.createStatement();
                ans = query.executeQuery("SELECT * FROM ATTRIBUTES "
                        + "WHERE OBJECT_TYPE_ID='" + object_type_id + "' AND "
                        + "NAME='" + name + "'");
                if (ans.next()) {
                    return new Long(ans.getLong(Consts.ATTRIBUTES__ATTRIBUTE_ID));
                } else {
                    throw new FinderException("SecB.getAttrID: not attr_id for " + name);
                }
            } finally {
                ans.close();
                query.close();
                conn.close();
            }
        } catch (Exception e) {
            throw new RemoteException("SecB.getAttr_id: " + e.toString());
        }
    }

    public java.lang.Long ejbFindByPrimaryKey(java.lang.Long aKey) throws FinderException {
        return aKey;
    }

    /**
     * @see javax.ejb.EntityBean#setEntityContext(javax.ejb.EntityContext)
     */
    public void setEntityContext(EntityContext aContext) {
        context = aContext;
    }

    /**
     * @see javax.ejb.EntityBean#unsetEntityContext()
     */
    public void unsetEntityContext() {
        context = null;
    }

    public void ejbLoad() throws EJBException, RemoteException {
        sessionKey = (Long) context.getPrimaryKey();
        permissionCache = new Hashtable();
        if (sessionKey.compareTo(SecurityST.LoginSessionKey) == 0) {
            curUser = null;
        } else {
            curUser = (UserRemote) ModelFactory.getByKey(sessionKey, sessionKey);
        }
    }

    public void ejbStore() throws EJBException, RemoteException {
    }

    public boolean setPermission(Long objkey, Long userkey, String entity, String field, String act, boolean access) throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean dropAllPermission(Long user, Long dropfor) throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean dropPermission(Long entityKey, Long dropfor, String entity, String field, String act) throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean dropPermission(Long entityKey, String entity, String field, String act) throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void setProtectOn(Long entityKey) throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean getPermission(Long objkey, String entity, String field, String act) throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
